﻿using System;
using System.Data;
namespace DAO
{
    public class ThongKeDAO
    {
        public static DataTable ThongKeDaXN(string tungay, string denngay, string dviGui)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_ThongKeDaXN ";
            sp += String.Format(" '{0}','{1}','{2}'", tungay, denngay, dviGui);
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable ThongKeDaXN(string tungay, string denngay)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_ThongKeDaXNNgay ";
            sp += String.Format(" '{0}','{1}'", tungay, denngay);
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable TimKetQua(string tungay, string denngay)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetKetQua ";
            sp += String.Format(" '{0}','{1}'", tungay, denngay);
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable BC_ThuLao_TH(string tungay, string denngay
                                            , int LoaiDVu, int Loai)
        {
            ///usp_GPB_BCThuLaoTongHop_All
            DataTable dtKQ = new DataTable();
            string sp = "";
            if (Loai == 1)
            {
                if (LoaiDVu == 9)
                {
                    sp = "usp_GPB_BCThuLaoTongHop_All";
                    sp += String.Format(" '{0}','{1}', {2}", tungay, denngay, LoaiDVu);
                }
                else
                {
                    sp = "usp_GPB_BCThuLaoTongHop  ";
                    sp += String.Format(" '{0}','{1}', {2}", tungay, denngay, LoaiDVu);
                }
            }
            else if (Loai == 5)            
            {
                if (LoaiDVu == 9)
                {
                    sp = "usp_GPB_BCXN_ThuLaoTongHop_All";
                    sp += String.Format(" '{0}','{1}', {2}", tungay, denngay, LoaiDVu);
                }
                else
                {
                    sp = "usp_GPB_BCXN_ThuLaoTongHop  ";
                    sp += String.Format(" '{0}','{1}', {2}", tungay, denngay, LoaiDVu);
                }
            }
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable BC_ThuLao_Liqui_TH(string tungay, string denngay)
        {
            ///usp_GPB_BCThuLaoTongHop_All
            DataTable dtKQ = new DataTable();
            string sp = "";

            sp = "usp_GPB_BCThuLaoTongHop_TP18 ";
            sp += String.Format(" '{0}','{1}'", tungay, denngay);
           
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable BC_ThuLao_THXN(string tungay, string denngay)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_BCThuLaoTongHopXN ";
            sp += String.Format(" '{0}','{1}'", tungay, denngay);
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable BC_ThuLao_Liqui_CT(string tungay, string denngay)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_BCThuLao_KQ_CT_TP18 ";
            sp += String.Format(" '{0}','{1}'", tungay, denngay);
            
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable BC_ThuLao_CT(string tungay, string denngay)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_BCThuLao_CT ";
            sp += String.Format(" '{0}','{1}'", tungay, denngay);

            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable BC_ThuLao_KQ_CT(string tungay, string denngay
                                                , int LoaiDVu, int LoaiBC)
        {
            DataTable dtKQ = new DataTable();
            string sp = "";
                         
            if (LoaiDVu == 9)
            {
                sp = "usp_GPB_BCThuLao_KQ_CT_All ";
                sp += String.Format(" '{0}','{1}'", tungay, denngay);
            }
            else
            {
                sp = "usp_GPB_BCThuLao_KQ_CT  ";
                sp += String.Format(" '{0}','{1}', {2}", tungay, denngay, LoaiDVu);
            }           

            dtKQ = Uit.it_SQL.getDataTable(sp);
            dtKQ.DefaultView.Sort = "SoTieuBan";
            dtKQ = dtKQ.DefaultView.ToTable();
            return dtKQ;
        }

        public static DataTable BC_ThuLao_XN_CT(string tungay, string denngay
                                                , int LoaiDVu, int LoaiBC)
        {
            DataTable dtKQ = new DataTable();
            string sp = "";

            if (LoaiDVu == 9)
            {
                sp = "usp_GPB_BCThuLao_KQXN_CT_All ";
                sp += String.Format(" '{0}','{1}'", tungay, denngay);
            }
            else
            {
                sp = "usp_GPB_BCThuLao_KQXN_CT  ";
                sp += String.Format(" '{0}','{1}', {2}", tungay, denngay, LoaiDVu);
            }

            dtKQ = Uit.it_SQL.getDataTable(sp);
            dtKQ.DefaultView.Sort = "SoTieuBan";
            dtKQ = dtKQ.DefaultView.ToTable();
            return dtKQ;
        }

        public static DataTable BC_ThuLaoCatLanh_KTV(string tungay, string denngay)
        {
            DataTable dtKQ = new DataTable();
            string sp = " "
                + "SELECT                             \n"
                + "	xn.SoHS                           \n"
                + "	,xn.SoBN                          \n"
                + "	,xn.DVuCode                       \n"
                + "	,xn.SoTieuBan                     \n"
                + "	,xn.HoTen                         \n"
                + "	,xn.NamSinh                       \n"
                + "	,xn.CDate	                      \n"
                + "	,dv.DonGia                        \n"
                + "	, 1 AS Slg	                      \n"
                + "	,ktv.HoTen    AS BS               \n"
                + "FROM GPB_NHANVATPHAM AS xn         \n"
                + "	INNER JOIN GPB_PHANLOAIDVU AS pl  \n"
                + "								ON pl.DVuCode = xn.DVuCode     \n"
                + "								AND pl.IDLoai = 1	           \n"
                + "	INNER JOIN GPB_DICHVU AS dv                                \n"
                + "						ON dv.DVuCode = xn.DVuCode             \n"
                + "	INNER JOIN GPB_KETQUAGPB AS kq                             \n"
                + "							ON kq.SoBN = xn.SoBN               \n"
                + "							AND kq.SoTieuBan = xn.SoTieuBan    \n"
                + "							AND kq.Lan = 1                     \n"
                + "	INNER JOIN GPB_KTV AS ktv ON ktv.IDKTV = kq.IDKTV          \n"
                + "WHERE            	                                       \n"
                + " kq.CDate >= '" + tungay + "'	                           \n"
                + " AND kq.CDate <= '" + denngay + "'	                           \n"
                + "ORDER BY kq.CDate	                                       \n";
           
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable BC_ThuLaoCatLanh_BS(string tungay, string denngay)
        {
            DataTable dtKQ = new DataTable();
            string sp = " "
                + "SELECT                             \n"
                + "	xn.SoHS                           \n"
                + "	,xn.SoBN                          \n"
                + "	,xn.DVuCode                       \n"
                + "	,xn.SoTieuBan                     \n"
                + "	,xn.HoTen                         \n"
                + "	,xn.NamSinh                       \n"
                + "	,xn.CDate	                      \n"
                + "	,dv.DonGia                        \n"
                + "	, 1 AS Slg	                      \n"
                + "	,bs.HoTen AS BS                   \n"
                + "FROM GPB_NHANVATPHAM AS xn         \n"
                + "	INNER JOIN GPB_PHANLOAIDVU AS pl  \n"
                + "								ON pl.DVuCode = xn.DVuCode     \n"
                + "								AND pl.IDLoai = 1	           \n"
                + "	INNER JOIN GPB_DICHVU AS dv                                \n"
                + "						ON dv.DVuCode = xn.DVuCode             \n"
                + "	INNER JOIN GPB_KETQUAGPB AS kq                             \n"
                + "							ON kq.SoBN = xn.SoBN               \n"
                + "							AND kq.SoTieuBan = xn.SoTieuBan    \n"
                + "							AND kq.Lan = 1                     \n"
                + "	INNER JOIN utb_DM_BACSI AS bs ON bs.IDBS = kq.IDBacSiTH    \n"
                + "WHERE            	                                       \n"
                + " kq.CDate    >= '" + tungay + "'	                           \n"
                + " AND kq.CDate <= '" + denngay + "'	                       \n"
                + "ORDER BY kq.CDate	                                       \n";

            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }
    }
}
